CREATE DATABASE
CREATE DATABASE — Create a new database
Synopsis
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LOCALE [=] locale ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ] ]
Description
CREATE DATABASE creates a new database, also known as a container.
To create a database, you must be a superuser or have the special CREATEDB privilege. See CREATE ROLE.
By default, the new database is created by cloning the standard system database template1. A different template can be specified by writing TEMPLATE name. In particular, by writing TEMPLATE template0, you can create a pristine database (one with no user-defined objects and unchanged system objects) that contains only the standard objects. This is helpful if you want to avoid copying any local installation objects that may have been added to template1.
Parameters
name
The name of the database to be created.
user_name
The role name of the user who will own the new database, or DEFAULT to use the default value (i.e., the user executing the command). To create a database owned by another role, you must be a direct or indirect member of that role, or a superuser.
template
The name of the template from which to create the new database, or DEFAULT to use the default template (template1).
encoding
The character set encoding to use in the new database. Specify a string constant (e.g., 'SQL_ASCII'), an integer encoding number, or DEFAULT to use the default encoding (i.e., the template database's encoding).
locale
This is a shortcut for setting both LC_COLLATE and LC_CTYPE at once. If this parameter is specified, you cannot specify either of those parameters individually.
[TABLE]
lc_collate
The collation order (LC_COLLATE) to use in the new database. This affects the sort order applied to strings, for example in queries with ORDER BY, and the order used by indexes on text columns. The default is to use the template database's collation order. See additional restrictions below.
lc_ctype
The character classification (LC_CTYPE) to use in the new database. This affects the categorization of characters, such as lowercase, uppercase, and digits. The default is to use the template database's character classification. See additional restrictions below.
tablespace_name
The name of the tablespace to associate with the new database, or DEFAULT to use the template database's tablespace. This tablespace will be the tablespace for objects created in this database. See CREATE TABLESPACE for details.
allowconn
If false, no one can connect to this database. The default is true, meaning connections are allowed (subject to other constraints such as GRANT/REVOKE CONNECT).
connlimit
How many concurrent connections are allowed to this database. -1 (the default) means no limit.
istemplate
If true, any user with the CREATEDB privilege can clone from this database. If false (the default), only superusers or the database owner can clone it.
The optional parameters can be written in any order, not necessarily in the order shown above.
Notes
CREATE DATABASE cannot be executed within a transaction block.
Errors with the message "could not initialize database directory" are mostly related to insufficient permissions on the data directory, a full disk, or other file system issues.
Use DROP DATABASE to remove a database.
The program createdb is a wrapper around this command, provided for convenience.
Database-level configuration parameters (see ALTER DATABASE) and database-level permissions (see GRANT) are not copied from the template database.
Although you can copy from a database other than template1 by specifying it as a template, this is (not yet) a general-purpose "COPY DATABASE" feature. The main limitation is that no other sessions can be connected to the template database while it is being copied. If any other connections exist when CREATE DATABASE starts, it will fail. Otherwise, new connections to the template database will be blocked until CREATE DATABASE completes.
The character set encoding specified for the new database must be compatible with the selected locale settings (LC_COLLATE and LC_CTYPE). If the locale is C (or equivalently POSIX), all encodings are allowed, but for other locale settings only one encoding works correctly (however, on Windows, UTF-8 encoding can be used with any locale). CREATE DATABASE will allow superusers to specify the SQL_ASCII encoding regardless of the locale setting, but this choice is deprecated and may cause string functions to behave incorrectly when data is not encoding-compatible with the locale stored in the database.
The encoding and locale settings must match the template database's encoding and locale, unless template0 is used as the template. This is because other databases may contain data that does not match the specified encoding, or may contain indexes whose sort order is affected by LC_COLLATE and LC_CTYPE. Copying such data would result in a corrupted database due to the new settings. However, template0 will not contain any data or indexes that could be affected.
The CONNECTION LIMIT option is only approximately enforced; if two new sessions start at roughly the same time and the database has only one connection "slot" remaining, both may fail. Also, the limit does not apply to superusers or background worker processes.
Examples
# To create a new database:
CREATE DATABASE lusiadas;
# To create a new database sales owned by user salesapp in the default tablespace salesspace:
CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
# To create database music with a different locale:
CREATE DATABASE music
LOCALE 'sv_SE.utf8'
TEMPLATE template0;
# In this example, the TEMPLATE template0 clause is required if the specified locale differs from the one in template1. (If not, specifying the locale explicitly is redundant.)
# To create database music2 with a different locale and a different character set encoding:
CREATE DATABASE music2
LOCALE 'sv_SE.iso885915'
ENCODING LATIN9
TEMPLATE template0;
# The specified locale and encoding settings must match, otherwise an error will be reported.
# Note that locale names are specific to the operating system, so the above commands may not work the same way everywhere.